package cs.xwi7xwa.data;

import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;

public class FactDbAdapter extends DatabaseAdapter
{
    public static final String KEY_ID = "_id";
    public static final String KEY_DESC = "description";
    public static final String KEY_ISHIDDEN = "isHidden";
    public static final String KEY_EXHIBITID = "exhibitId";
    public static final String KEY_NUMBER = "factNumber";    
    public static final String DATABASE_TABLE = "fact";
    
    public static final int KEY_ISHIDDEN_COLUMN = 2;
    public static final int KEY_ID_COLUMN = 0;
    public static final int KEY_EXHIBITID_COLUMN = 3;
    public static final int KEY_DESC_COLUMN = 1;

    public FactDbAdapter(Context context)
    {
        super(context);
    }

    /**
     * Return a Cursor positioned at the fact that matches the given id under
     * the given exhibit
     * 
     * @param id
     *            id of fact to retrieve
     * @param exhibitId
     *            id of the given exhibit
     * @return Cursor positioned to matching fact, if found
     * @throws SQLException
     *             if note could not be found/retrieved
     */
    public Cursor fetch(int id, int exhibitId) throws SQLException
    {
        Cursor cursor = db.query(DATABASE_TABLE, new String[] { KEY_ID,
                KEY_DESC, KEY_ISHIDDEN, KEY_EXHIBITID }, KEY_ID + "=" + id
                + " AND " + KEY_EXHIBITID + "=" + exhibitId, null, null, null,
                null);
        if (cursor != null)
        {
            cursor.moveToFirst();
        }
        return cursor;
    }
    
    /**
     * Return a Cursor positioned at the fact that matches the given id
     * 
     * @param id
     *            id of fact to retrieve
     * @return Cursor positioned to matching fact, if found
     * @throws SQLException
     *             if note could not be found/retrieved
     */
    public Cursor fetch(int id) throws SQLException
    {
        Cursor cursor = db.query(DATABASE_TABLE, new String[] { KEY_ID,
                KEY_DESC, KEY_ISHIDDEN, KEY_EXHIBITID }, KEY_ID + "=" + id,
                null, null, null, null);
        if (cursor != null)
        {
            cursor.moveToFirst();
        }
        return cursor;
    }

    /**
     * Return a Cursor over the list of all facts in the database under the
     * given exhibit
     * 
     * @param exhibitId
     *            id of the given exhibit
     * @return Cursor over all facts under the given exhibit
     */
    public Cursor fetchAll(int exhibitId)
    {
        return db.query(DATABASE_TABLE, new String[] { KEY_ID, KEY_DESC,
                KEY_ISHIDDEN, KEY_EXHIBITID }, KEY_EXHIBITID + "=" + exhibitId,
                null, null, null, null);
    }

    /**
     * Return a Cursor over the list of all facts in the database
     * 
     * @return Cursor over all facts under the given exhibit
     */
    public Cursor fetchAll()
    {
        return db.query(DATABASE_TABLE, new String[] { KEY_ID, KEY_DESC,
                KEY_ISHIDDEN, KEY_EXHIBITID }, null, null, null, null, null);
    }

    /**
     * Return a Cursor over a list of {Fact's id, Exhibit's image path,
     * Exhibit's name, Fact's description} tuple for all visible facts where
     * exhibit is the parent exhibit of each fact.
     * 
     * @return Cursor over the tuples
     */
    public Cursor fetchVisibleFactExhibit()
    {
        return fetchFactsExhibit(-1, null, false);
    }
    
    public Cursor fetchCollectedFacts(List<Integer> exclude)
    {
        return fetchFactsExhibit(-1, exclude, false);
    }
    
    private Cursor fetchFactsExhibit(int factId, List<Integer> exclude,
            boolean isHidden)
    {
        // SELECT fact._id, exhibit.image, exhibit.name, fact.description
        // FROM exhibit, fact WHERE fact.exhibitId=exhibit.id
        StringBuilder sql = new StringBuilder("SELECT ");
        sql.append(DATABASE_TABLE).append(".").append(KEY_ID).append(", ");
        sql.append(ExhibitDbAdapter.DATABASE_TABLE).append(".").append(
                ExhibitDbAdapter.KEY_IMAGE).append(", ");
        sql.append(ExhibitDbAdapter.DATABASE_TABLE).append(".").append(
                ExhibitDbAdapter.KEY_NAME).append(", ");
        sql.append(DATABASE_TABLE).append(".").append(KEY_DESC).append(", ");
        sql.append(DATABASE_TABLE).append(".").append(KEY_NUMBER);
        sql.append(" FROM ").append(ExhibitDbAdapter.DATABASE_TABLE).append(
                ", ");
        sql.append(DATABASE_TABLE);
        sql.append(" WHERE ").append(DATABASE_TABLE).append(".").append(
                KEY_EXHIBITID);
        sql.append("=").append(ExhibitDbAdapter.DATABASE_TABLE).append(".")
                .append(ExhibitDbAdapter.KEY_ID);

        // If factId exists, then add fact._id=id
        if (factId > 0)
        {
            sql.append(" AND ").append(DATABASE_TABLE).append(".").append(
                    KEY_ID);
            sql.append("=").append(factId);
        }

        // If exclude list exist, then add all ids in the list as excluded
        if (exclude != null)
        {
            for (Integer id : exclude)
            {
                sql.append(" AND ").append(DATABASE_TABLE).append(".").append(
                        KEY_ID);
                sql.append("<>").append(id);
            }
        }

        // Set whether to return hidden facts or visible ones
        sql.append(" AND ").append(DATABASE_TABLE).append(".").append(
                KEY_ISHIDDEN);
        sql.append("=").append(isHidden ? 1 : 0);

        sql.append(" ORDER BY ").append(DATABASE_TABLE).append(".").append(
            KEY_EXHIBITID).append(" ASC");
        
        Cursor cursor = db.rawQuery(sql.toString(), null);

        // Move the pointer to the first element
        if (cursor != null)
        {
            cursor.moveToFirst();
        }
        
        // Return cursor iff there is at least one row that matches the query
        if (cursor.getCount() > 0)
        {
            return cursor;
        }
        else
        {
            return null;
        }
    }
    
    /**
     * Return a Cursor positioned at a {Fact's id, Exhibit's image path,
     * Exhibit's name, Fact's description} tuple for the fact where exhibit is
     * the parent exhibit of each fact.
     * 
     * @param id
     *            id of the fact
     * @return Cursor over the tuples
     */
    public Cursor fetchHiddenFactExhibit(int id)
    {
        return fetchFactsExhibit(id, null, true);
    }
    
    public int getFactNumber(int factId, int exhibitId)
    {
        Cursor cursor = db.query(DATABASE_TABLE, new String[] { KEY_ID },
                KEY_EXHIBITID + "=" + exhibitId + " AND " + KEY_ID + "<"
                        + factId, null, null, null, null);

        if (cursor != null)
        {
            return cursor.getCount() + 1;
        }
        else
        {
            return 0;
        }
    }
    
    /**
     * Change the isHidden value of the fact. The fact to be updated is
     * specified using the id, and its isHidden value is altered according to
     * the boolean value passed in.
     * 
     * @param id
     *            id of fact to update
     * @param isHidden
     *            whether the fact is hidden or not
     * @return true if the note was successfully updated, false otherwise
     */
    public boolean setHidden(int id, boolean isHidden)
    {
        ContentValues args = new ContentValues();
        args.put(KEY_ISHIDDEN, isHidden ? 1 : 0);

        return db.update(DATABASE_TABLE, args, KEY_ID + "=" + id, null) > 0;
    }
}
